import datetime
import subprocess
import random
import redis
import time
import xlwt
from time import sleep, strftime
from playwright.sync_api import sync_playwright
import pandas as pd
import numpy as np
import pymysql.cursors
from parsel import Selector
import gc

db = pymysql.connect(host="localhost",
                     user='root',
                     port=3306,
                     password='123456',
                     database='big_data')
cur = db.cursor()
redis_con = redis.Redis(host='121.4.69.146', password='xhhg123456',
                        port=6389, db=2, decode_responses=True)


def select_yiyuan():
    sql = 'SELECT yiyuan FROM yiyuan_yizhuaqu'
    cur.execute(sql)
    data = cur.fetchall()
    have_already_done = set([sqh[0].replace("ZL", '') for sqh in data])
    return have_already_done


def scripy(page, zhuanli_index, inst, yiyuan):
    # 解析申请信息
    sqh = 'ZL' + zhuanli_index
    try:
        page_soup = Selector(page.content())
    except:
        page.reload()
        page.wait_for_load_state('networkidle')
        page_soup = Selector(page.content())
    patent_state = page_soup.xpath(
        'normalize-space(//div[text()="案件状态"]/following-sibling::div[1]/text())').extract_first()
    if not patent_state:
        patent_state = ''
    patent_name = page_soup.xpath(
        'normalize-space(//div[text()="发明名称"]/following-sibling::div[1]/text())').extract_first()
    if not patent_name:
        patent_name = ''
    sq_date = page_soup.xpath(
        'normalize-space(//div[text()="申请日"]/following-sibling::div[1]/text())').extract_first()
    if not sq_date:
        sq_date = ''
    fm_class = page_soup.xpath(
        'normalize-space(//div[text()="主分类号"]/following-sibling::div[1]/text())').extract_first()
    if not fm_class:
        fm_class = ''
    agency = page_soup.xpath(
        'normalize-space(//th[text()="代理机构名称"]/../../following-sibling::tbody[1]/tr/td[1]/text())').extract_first()
    if not agency:
        agency = ''
    dlr = page_soup.xpath(
        'normalize-space(//th[text()="代理机构名称"]/../../following-sibling::tbody[1]/tr/td[2]/text())').extract_first()
    if not dlr:
        dlr = ''
    sqr = page_soup.xpath(
        'normalize-space(//p[text()="申请人"]/../following-sibling::div[1]//table/tbody/tr/td[1]/text())').extract_first()
    if not sqr:
        sqr = ''
    fmr = page_soup.xpath(
        'normalize-space(//div[text()="发明人姓名"]/following-sibling::div[1]/text())').extract_first()
    if not fmr:
        fmr = ''
    fm_num = zhuanli_index[4]
    if fm_num == '1':
        fm_type = '发明'
    elif fm_num == '2':
        fm_type = '实用'
    elif fm_num == '3':
        fm_type = '外观'
    sql = f"""INSERT INTO yiyuan_pantent_status_quchon (application_number, case_status, fm_name,fm_class, sq_date,  agency, patent_user, first_agent, fmr, port,yiyuan,fm_type) VALUES
    ('{sqh}', '{patent_state}', '{patent_name}', '{fm_class}', '{sq_date}', '{agency}', '{sqr}', '{dlr}', '{fmr}', '{inst}','{yiyuan}','{fm_type}')"""
    print(sql)
    try:
        cur.execute(sql)
        db.commit()
    except Exception as e:
        print(e)
        time.sleep(60)
        cur.execute(sql)
        db.commit()


def select_sqh():
    sql = 'SELECT application_number FROM xhhg_pantent_status_0504'
    cur.execute(sql)
    data = cur.fetchall()
    have_already_done = set([sqh[0].replace("ZL", '') for sqh in data])
    return have_already_done


def run(i, port, inst, yiyuan_list, have_already_done):
    # 使用账号密码登录
    port_code_dict = {'XHHG': (r'42264', r'Xhhg2019@'), 'XH': (
        '12420000420007938Y', 'XHYYkyc85755457@'), 'WB': ('13283766953', 'Ws123456+++')}
    user, passwd = port_code_dict.get(inst)
    with sync_playwright() as p:
        # browser = p.chromium.launch(headless=False)
        browser = p.firefox.launch(headless=False)
        context = browser.new_context()
        context.set_default_timeout(6000000)
        context.set_default_navigation_timeout(6000000)
        page = context.new_page()
        page.set_default_timeout(6000000)
        page.set_default_navigation_timeout(6000000)
        page.goto('https://tysf.cponline.cnipa.gov.cn/am/#/user/login')
        page.wait_for_load_state('domcontentloaded')
        if inst == 'XHHG':
            page.click('xpath=//span[text()="代理机构登录"]')
            time.sleep(1)
            page.click('xpath=//input[@placeholder="代理机构代码"]')
            time.sleep(0.5)
            page.fill('xpath=//input[@placeholder="代理机构代码"]', user)
            time.sleep(1)
        elif inst == 'XH':
            page.click('xpath=//span[text()="法人登录"]')
            time.sleep(1)
            page.click('xpath=//input[@placeholder="统一社会信用代码"]')
            time.sleep(0.5)
            page.fill('xpath=//input[@placeholder="统一社会信用代码"]', user)
            time.sleep(1)
        elif inst == 'WB':
            page.click('xpath=//span[text()="自然人登录"]')
            time.sleep(1)
            page.click('xpath=//input[@placeholder="手机号/证件号码"]')
            time.sleep(0.5)
            page.fill('xpath=//input[@placeholder="手机号/证件号码"]', user)
            time.sleep(1)
        page.click('xpath=//input[@placeholder="请输入密码"]')
        time.sleep(0.5)
        page.fill('xpath=//input[@placeholder="请输入密码"]', passwd)
        time.sleep(0.5)
        page.click('xpath=//span[text()="登录"]')
        time.sleep(15)
        # input('%s-滑动验证登录网站：' % i)
        page.goto('https://cpquery.cponline.cnipa.gov.cn/chinesepatent/index')
        page.wait_for_load_state()
        time.sleep(3)
        if '关于中国及多国专利审查信息查询系统新旧系统双运行模式的通知' in page.content():
            page.click(
                'xpath=//span[@class="q-btn__content text-center col items-center q-anchor--skip justify-end row"]')
        time.sleep(5)
        input('选择日期：')
        for yiyuan in yiyuan_list:
            if yiyuan not in have_already_done:
                print(f'@@开始查询医院：{yiyuan}@@')
                page.fill(
                    'xpath=//div[text()="申请人："]/../label//input[@placeholder="请输入"]', yiyuan)
                time.sleep(1)
                page.click('xpath=//span[text()="查询"]/../../..')
                time.sleep(5)
                page.wait_for_load_state('networkidle')
                # 解析全部专利
                page_soup = Selector(page.content())
                all_nums = page_soup.xpath(
                    '//div[@class="total"]/strong/text()').extract_first()
                all_nums = int(all_nums)
                print(f'@@{yiyuan}:{all_nums}@@')
                if yiyuan=='宜昌市第一人民医院':
                    page_num=7
                    input(f'点击页面：{page_num}')
                if yiyuan=='华中科技大学同济医学院附属协和医院':
                    page_num=313
                    input(f'点击页面：{page_num}')                
                elif yiyuan=='十堰市太和医院':
                    page_num=20
                    input(f'点击页面：{page_num}')
                elif yiyuan=='武汉大学中南医院':
                    page_num=52
                    input(f'点击页面：{page_num}')
                else:
                    page_num = 1
                if all_nums > 0:
                    import math
                    max_page = math.ceil(all_nums / 10)
                    print(f'max_page:{max_page}')
                    while page_num<=max_page:
                        print(f'@@开始抓取{yiyuan}:第{page_num}页专利数据@@')
                        page_soup = Selector(page.content())
                        sqh_list = page_soup.xpath(
                            '//span[contains(text(),"申请号/专利号：")]/following-sibling::span[1]/text()').extract()
                        for index, sqh in enumerate(sqh_list):
                            url = f'https://cpquery.cponline.cnipa.gov.cn/detail/index?zhuanlisqh={sqh}&anjianbh'
                            with context.expect_page() as page_sqh_info:
                                page.evaluate(f'''window.open('{url}')''')
                            page_sqh = page_sqh_info.value
                            page_sqh.set_default_timeout(6000000)
                            page_sqh.set_default_navigation_timeout(6000000)
                            page_sqh.wait_for_load_state('networkidle')
                            scripy(page_sqh, sqh, inst, yiyuan)
                            page_sqh.close()
                            del page_sqh
                            
                            gc.collect()
                            print(f'---{yiyuan}_第{page_num}页_{sqh}_抓取完成---')
                        # 点击下一页
                        page_num+=1
                        if page_num<=max_page:
                            page.click(
                                f'xpath=//span[@class="block" and text()={page_num}]/..')
                            # time.sleep(5)
                            page.wait_for_load_state('networkidle')
                cur.execute(f'''INSERT INTO yiyuan_yizhuaqu(yiyuan)VALUES('{yiyuan}')''')
                db.commit()

def chaxun_zhuanli(port, inst, sqh_yiyuan_list):
    # 使用账号密码登录
    port_code_dict = {'XHHG': (r'42264', r'Xhhg2019@'), 'XH': (
        '12420000420007938Y', 'XHYYkyc85755457@'), 'WB': ('13283766953', 'Ws123456+++')}
    user, passwd = port_code_dict.get(inst)
    with sync_playwright() as p:
        # browser = p.chromium.launch(headless=False)
        browser = p.firefox.launch(headless=False)
        context = browser.new_context()
        context.set_default_timeout(6000000)
        context.set_default_navigation_timeout(6000000)
        page = context.new_page()
        page.set_default_timeout(6000000)
        page.set_default_navigation_timeout(6000000)
        page.goto('https://tysf.cponline.cnipa.gov.cn/am/#/user/login')
        page.wait_for_load_state('domcontentloaded')
        if inst == 'XHHG':
            page.click('xpath=//span[text()="代理机构登录"]')
            time.sleep(1)
            page.click('xpath=//input[@placeholder="代理机构代码"]')
            time.sleep(0.5)
            page.fill('xpath=//input[@placeholder="代理机构代码"]', user)
            time.sleep(1)
        elif inst == 'XH':
            page.click('xpath=//span[text()="法人登录"]')
            time.sleep(1)
            page.click('xpath=//input[@placeholder="统一社会信用代码"]')
            time.sleep(0.5)
            page.fill('xpath=//input[@placeholder="统一社会信用代码"]', user)
            time.sleep(1)
        elif inst == 'WB':
            page.click('xpath=//span[text()="自然人登录"]')
            time.sleep(1)
            page.click('xpath=//input[@placeholder="手机号/证件号码"]')
            time.sleep(0.5)
            page.fill('xpath=//input[@placeholder="手机号/证件号码"]', user)
            time.sleep(1)
        page.click('xpath=//input[@placeholder="请输入密码"]')
        time.sleep(0.5)
        page.fill('xpath=//input[@placeholder="请输入密码"]', passwd)
        time.sleep(0.5)
        page.click('xpath=//span[text()="登录"]')
        time.sleep(15)
        # input('%s-滑动验证登录网站：' % i)
        page.goto('https://cpquery.cponline.cnipa.gov.cn/chinesepatent/index')
        page.wait_for_load_state('networkidle')
        time.sleep(5)
        if '关于中国及多国专利审查信息查询系统新旧系统双运行模式的通知' in page.content():
            page.click(
                'xpath=//span[@class="q-btn__content text-center col items-center q-anchor--skip justify-end row"]')
        time.sleep(5)
        for sqh,yiyuan in sqh_yiyuan_list:
            print(f'--开始抓取{yiyuan}_{sqh}--')
            url = f'https://cpquery.cponline.cnipa.gov.cn/detail/index?zhuanlisqh={sqh}&anjianbh'
            page.goto(url)
            page.wait_for_load_state('networkidle')
            scripy(page, sqh, inst, yiyuan)

if __name__ == '__main__':
    # port = "XH"
    have_already_done = select_yiyuan()
    port_dict = {'XHHG': '信合红谷接口查询', 'XH': '协和端口查询', 'WB': "医院"}
    inst = 'WB'
    port = port_dict.get(inst)
    s_time = time.time()
    import pandas as pd
    df = pd.read_excel('漏掉的专利状态.xlsx')
    sqh_yiyuan_list = df.to_dict('split').get('data')
    chaxun_zhuanli(port, inst, sqh_yiyuan_list)
    db.close()
    en_time = time.time()
    print(f"本次耗时: {en_time - s_time}")
